CREATE DATABASE CE_MANI

USE CE_MANI;

SELECT * FROM USER_DB
SELECT * FROM PERMISO_USER
WHERE COD_USR='' AND PARAMET=''


CREATE TABLE CONFIGURACION_USER(
    COD_USR     VARCHAR(20),
    PARAMET     VARCHAR(50),
    ESTATUS     BOOLEAN
)


CREATE TABLE PARAMETROS (
    COD_PAR     VARCHAR(50)
)

CREATE TABLE USER_DB
(
    COD_USR     VARCHAR(20),
    PSW_USR     VARCHAR(200),
    TIPE        INT,
    PRIMARY KEY(COD_USR)
)


CREATE TABLE PERMISO_USER
(
    COD_USR     VARCHAR(20),
    PARAMET     VARCHAR(50),
    ESTATE      BIT,
    FOREIGN KEY(COD_USR) REFERENCES USER_DB(COD_USR)
)

CREATE TABLE CLIENTES
(
    COD_CLIE    VARCHAR(10),
    NOMBRE      VARCHAR(100),
    TELEFONO    VARCHAR(20),
    CORREO      VARCHAR(50),
    UBICACION   VARCHAR(100),
PRIMARY KEY(COD_CLIE)
)


CREATE TABLE PROVEEDOR
(
    COD_PROV    VARCHAR(10),
    NOMBRE      VARCHAR(200),
    TELEFONO    VARCHAR(20),
    CORREO      VARCHAR(50),
    UBIACION    VARCHAR(100),
    PRIMARY KEY(COD_PROV)
)

CREATE TABLE MPRIMA
(
    COD_MP      VARCHAR(10),
    COD_PV      VARCHAR(10),
    NOMBRE      VARCHAR(100),
    EXISTENCIA  FLOAT,
    COSTO       FLOAT,
    NPRODUC	FLOAT,
    CPRODUC	VARCHAR(10),
    PRIMARY KEY(COD_MP),
    FOREIGN KEY(COD_PV) 	REFERENCES PROVEEDOR(COD_PROV),
    FOREIGN KEY(CPRODUC)	REFERENCES PRODUCTO(COD_PDT)
)

INSERT INTO MPRIMA VALUES('CMP','CPR','NOMBRE','EXIST','COST','CANTPROD','CPROD')

CREATE TABLE COMPRA
(
    COD_CP      VARCHAR(10),
    COD_PV      VARCHAR(10),
    FECHA_C     DATE,
    DOLAR       BIT,
    TASA        FLOAT,
    PRIMARY KEY(COD_CP),
    FOREIGN KEY(COD_PV) REFERENCES PROVEEDOR(COD_PROV)
)



CREATE TABLE DETAIL_COMPRA
(
    C_DCP       INT AUTO_INCREMENT,
    C_CP        VARCHAR(10),
    C_MP        VARCHAR(10),
    UNID        FLOAT,
    COST        FLOAT,
    PRIMARY KEY(C_DCP),
    FOREIGN KEY(C_CP) REFERENCES COMPRA(COD_CP),
    FOREIGN KEY(C_MP) REFERENCES MPRIMA(COD_MP)
)

INSERT INTO DETAIL_COMPRA VALUES('C_COMPRA','C_MPRIMA',1,1)

CREATE TABLE PRODUCTO
(
    COD_PDT     VARCHAR(10),
    DESCRIP     VARCHAR(200),
    F_REGISTRO  DATE,
    UNIDAD      FLOAT,
    COSTO       FLOAT,
    ESTADO      INT,
    DIMENSIONES VARCHAR(200),
    PRIMARY KEY(COD_PDT)
)



UPDATE MPRIMA SET EXISTENCIA=0,COSTO=0 WHERE COD_MP='723ee9aec8'

SELECT * FROM MPRIMA

SELECT * FROM DETAIL_COMPRA
DELETE FROM DETAIL_COMPRA
CREATE TABLE HIST_PROD
(
	C_HP	INT AUTO_INCREMENT,
	CP		VARCHAR(10),
	FECHA	DATETIME,
        PRIMARY KEY(C_HP)
)

CREATE TABLE DTL_HP
(
	C_HP	INT,
	C_MP	VARCHAR(10),
	CANT	INT,
 	FOREIGN KEY(C_HP) REFERENCES HIST_PROD(C_HP)	
)

CREATE TABLE LOTE_PROD
(
    C_LPDRD     VARCHAR(10),
    C_PDT       VARCHAR(10),
    FECHA_I     DATETIME,
    FECHA_F     DATETIME,
    PRIMARY KEY(C_LPDRD),
    FOREIGN KEY(C_PDT) REFERENCES PRODUCTO(COD_PDT)
)


SELECT * FROM PRODUCTO
INSERT INTO PRODUCTO VALUES('COD_PDT','DESCRIP',NOW(),'UNIDAD','COSTO','ESTADO','DIMENSIONES')

SELECT * FROM USER_DB
INSERT INTO USER_DB VALUES ('RON',MD5('123'),0)


SELECT * FROM PERMISO_USER


SELECT * FROM PARAMETROS


SELECT * FROM MPRIMA

UPDATE MPRIMA
SET COSTO=23
WHERE COD_MP='d7e5d0216e'



SELECT COD_PDT,DESCRIP FROM PRODUCTO

SELECT * FROM PRODUCTO

INSERT INTO PRODUCTO VALUES('PDT1','PRODUCTO DE PRUEBA',NOW(),0,0,0,'GRANDE')

SELECT * FROM CLIENTES
INSERT INTO CLIENTES VALUES('COD','NOM','TEL','CORR','UBI')

UPDATE CLIENTES SET NOMBRE='',TELEFONO='',CORREO='',UBICACION='' WHERE COD_CLIE=''

SELECT * FROM COMPRA
SELECT * FROM DETAIL_COMPRA

SELECT COD_MP,NOMBRE,EXISTENCIA,COSTO FROM MPRIMA WHERE COD_PV=''



SELECT EXISTENCIA,COSTO FROM MPRIMA WHERE COD_MP='aab218b357'